package com.dubbo.mapper;

import com.dubbo.domain.Goods;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;
import java.util.Map;

/**
 * 商品Mapper
 * @author wujunyuan
 * @date 2018年12月15日  上午11:10:17
 */
public interface GoodsMapper extends Mapper<Goods>{

	/**
	 * 查询与该系列对应的商品列表
	 * @author wujunyuan
	 * @date 2018年12月15日  下午4:18:00
	 * @param categoryId
	 * @param pageTotal 
	 * @param pageStart 
	 * @return
	 */
	/*@Select("SELECT" +
			"	gc.id AS categoryId," + 
			"	g.id AS goodsId,"+
			"	g.title," + 
			"	g.pic," + 
			"	g.price," + 
			"	g.content" + 
			" FROM" + 
			"	g_category gc" + 
			" JOIN g_goods g ON g.categoryId = gc.id" + 
			" WHERE" + 
			"	g.categoryId = #{categoryId}" + 
			" AND g.status=1 "+
			" ORDER BY" + 
			"	CONVERT (" + 
			"		SUBSTR(g.title, 1, 2) USING gbk" + 
			"	), " + 
			"	CONVERT (" + 
			"		SUBSTR(g.title, 5, 1) USING gbk" + 
			"	) DESC," + 
			"	CONVERT (" + 
			"		SUBSTR(g.title, 7, 1) USING gbk" + 
			"	)"+
			" LIMIT #{pageStart},#{pageTotal}")
	List<Map<String, Object>> findMatchCategor(@Param("categoryId")Long categoryId,
			@Param("pageStart")Long pageStart, @Param("pageTotal")Long pageTotal);*/
	/**
	 * 查询与该系列对应的商品列表
	 * @author wujunyuan
	 * @date 2018年12月15日  下午4:18:00
	 * @param categoryId
	 * @param pageTotal 
	 * @param pageStart 
	 * @return
	 */
	@Select("SELECT " + 
			"	gc.id AS categoryId, " + 
			"	g.id AS goodsId, " + 
			"	g.title, " + 
			"	g.pic, " + 
			"	g.price, " + 
			"	g.content, " + 
			"	g.sort " + 
			"FROM " + 
			"	g_category gc " + 
			"JOIN g_goods g ON g.categoryId = gc.id " + 
			"WHERE " + 
			"	g.categoryId = #{categoryId} " + 
			"AND g. STATUS = 1 " + 
			"ORDER BY g.sort ASC " + 
			"LIMIT #{pageStart},#{pageTotal}")
	List<Map<String, Object>> findMatchCategor(@Param("categoryId") Long categoryId,
                                               @Param("pageStart") Long pageStart, @Param("pageTotal") Long pageTotal);

	/**
	 * 查询商品价格(与商品表同步)
	 * @author wujunyuan
	 * @date 2018年12月17日  下午4:43:21
	 * @param goodsid
	 * @return
	 */
	@Select("SELECT" + 
			"	g.price," +
			"	g.categoryId," +
			"   g.pic"+
			" FROM" + 
			"	g_goods g" + 
			" WHERE" + 
			"	g.id = #{goodsid}")
	Map<String,Object> findGoodsPrice(@Param("goodsid") String goodsid);

	
	/**
	 * 查询与该系列对应的商品总数
	 * @author wujunyuan
	 * @date 2019年1月9日  下午5:33:50
	 * @param categoryId
	 * @return
	 */
	@Select("SELECT" +
			"	COUNT(g.id)AS goodsId"+
			" FROM" + 
			"	g_category gc" + 
			" JOIN g_goods g ON g.categoryId = gc.id" + 
			" WHERE" + 
			"	g.categoryId = #{categoryId}" + 
			" AND g.status=1 "+
			" ORDER BY" + 
			"	CONVERT (" + 
			"		SUBSTR(g.title, 5, 1) USING gbk" + 
			"	) DESC," + 
			"	CONVERT (" + 
			"		SUBSTR(g.title, 7, 1) USING gbk" + 
			"	)")
	Long findMatchCategorCount(@Param("categoryId") Long categoryId);

}